﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.OleDb;
namespace DAO
{
    public class GiaoVienDAO : AbstractDAO<GiaoVienDTO>
    {
        public static GiaoVienDTO LayDuLieuTuDataRow(DataRow dr)
        {
            GiaoVienDTO item = null;
            try
            {
                item = new GiaoVienDTO();
                item.ID = dr["ID"].ToString();
                item.IDLoaiGiaoVien = dr["IDLoaiGiaoVien"].ToString();
                item.HoTen = dr["HoTen"].ToString();
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi khi lấy thông tin: " + ex.ToString());
            }
            return item;
        }

        //lấy danh sách đầy đủ
        public static DataTable LayDanhSachGVTheoLop(string idLop)
        {
            DataTable dt = null;
            try
            {
                String query = "SELECT gv.HoTen,(SELECT COUNT(*) FROM ThoiKhoaBieu tkb, ChiTietThoiKHoaBieu ct Where ct.IDTHoiKhoaBieu = tkb.ID and ct.IDGiaoVien = gv.ID) "
                + "From PhanCongGiangDay pc, GiaoVien gv "
                + "Where pc.IDGiaoVien = gv.ID and pc.IDLop = ? "
                + "Group by gv.ID, gv.HoTen";

                IList<OleDbParameter> arrParameter = new List<OleDbParameter>();
                arrParameter.Add(new OleDbParameter("@IDLop", idLop));

                dt = DataProvider.ExecuteNonQuery(query, arrParameter);

            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi khi lấy DS: " + ex.ToString());
            }
            return dt;
        }

        public static DataTable LayDanhSachGVTheoLop2(string idLop)
        {
            DataTable dt = null;
            try
            {
                String query = "Select gv.ID, gv.HoTen, lgv.ID From GiaoVien gv, LoaiGiaoVien lgv, PhanCongGiangDay pc "
                + " where gv.IDLoaiGiaoVien = lgv.ID and pc.IDGiaoVien = gv.ID and pc.IDLop = ?";

                IList<OleDbParameter> arrParameter = new List<OleDbParameter>();
                arrParameter.Add(new OleDbParameter("@IDLop", idLop));

                dt = DataProvider.ExecuteNonQuery(query, arrParameter);

            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi khi lấy DS: " + ex.ToString());
            }
            return dt;
        }

        protected override string GetQueryLayDanhSach()
        {
            return "select * from GiaoVien";
        }

        protected override string GetInsertQuery()
        {
            throw new NotImplementedException();
        }

        protected override string GetUpdateQuery()
        {
            throw new NotImplementedException();
        }

        protected override string GetDeleteQuery()
        {
            throw new NotImplementedException();
        }

        protected override string GetQueryGetByID(string id)
        {
            throw new NotImplementedException();
        }

        protected override IList<OleDbParameter> GetListParameter(GiaoVienDTO item)
        {
            throw new NotImplementedException();
        }

        protected override GiaoVienDTO GetObjectFromDataRow(DataRow dr)
        {
            GiaoVienDTO item = new GiaoVienDTO();
            item.ID = dr["ID"].ToString();
            item.IDLoaiGiaoVien = dr["IDLoaiGiaoVien"].ToString();
            item.HoTen = dr["HoTen"].ToString();
            return item;
        }

        public static DataTable LayDanhSachGVTheoKhoi(string idKhoi)
        {
            DataTable dt = null;
            try
            {
                String query = "SELECT gv.HoTen,(SELECT COUNT(*) FROM ThoiKhoaBieu tkb, ChiTietThoiKHoaBieu ct Where ct.IDTHoiKhoaBieu = tkb.ID and ct.IDGiaoVien = gv.ID) "
                + "From PhanCongGiangDay pc, GiaoVien gv, Lop l "
                + "Where pc.IDGiaoVien = gv.ID and pc.IDLop = l.ID and l.IDKhoi = ? "
                + "Group by gv.ID, gv.HoTen";

                IList<OleDbParameter> arrParameter = new List<OleDbParameter>();
                arrParameter.Add(new OleDbParameter("@IDKhoi", idKhoi));

                dt = DataProvider.ExecuteNonQuery(query, arrParameter);

            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi khi lấy DS: " + ex.ToString());
            }
            return dt;
        }
    }
}
